<?php
require_once "vos/LoginResultVO.php";

/**
 * UserClass 
 *
 * This class is designed to work with users of the system - 
 * authentication, access control, etc.
 *
 * @author Victor Magarlamov <victor.magarlamov@gmail.com>
 * @version 1.0
 * @package presstrack
 */
class UserClass 
{
	public $_explicitType = "presstrack.UserClass";    
	
	/**
     * user ID
     * @access public
     * @var integer
     */
	public $iUserId;
	
	/**
     * nickname for authentication
     * @access public
     * @var string
     */
  	public $mNick;
	
	/**
     * pass for authentication
     * @access public
     * @var string
     */
  	public $mPass;
	
	/**
     * full name
     * @access public
     * @var string
     */
  	public $mName;
	
	/**
     * section
     * @access public
     * @var int
     */
  	public $iSection;
	
	/**
     * user's contacts
     * @access public
     * @var array
     */
  	public $arContact;
	
	/**
     * mediator ID
     * @access public
     * @var int
     */
  	public $iMediumId;
	
	/**
     * statistic of user
     * @access public
     * @var object
     */
  	public $obStatistic;
	
	function __construct() 
	{		
    }
	
	/**
	 * ReadFromDb
	 *	 
     * set user's data from database
	 *
	 * @param	int	$iduser	user ID
     */
	function ReadFromDb($iduser)
	{
		set_time_limit(0);
		$sql = "SELECT name, medium, section FROM press_track_new.user "
			 . "WHERE iduser = $iduser";
		$query = mysql_query($sql);		
		if (mysql_errno())
		{
			return mysql_errno() . ": " . mysql_error();
		}
		
		$res 				= mysql_fetch_object($query);
		$this->iUserId		= $iduser;
		$this->iMediumId	= $res->medium;
		$this->mName		= $res->name;
		$this->iSection		= $res->section;			
	}
	
	/**
	 * GetContacts
	 *	 
     * collecting user contacts 
	 *
     */
	function GetContacts()
	{
		$sql = "SELECT * FROM press_track_new.contacts " 
			 . "WHERE user = $this->iUserId ORDER BY type";
		$query = mysql_query($sql);		
		if (mysql_errno())
		{
			return mysql_errno() . ": " . mysql_error();
		}		
		
		while ($contact = mysql_fetch_object($query))
		{		
			$this->arContact[] = $contact;
		}		
	}
	
	/**
	 * GetStatistic
	 *	 
     * @return	string	$mode		"by_year" or "by_month"
	 * @return	string	$year		year
	 * @return	string	$month		month
     */
	function GetStatistic($mode, $year, $month)
	{
		if ($mode == "by_year")
		{
			if ($this->iMediumId > -1)
			{
				$this->GetStatisticForCustomerForYear($year);
			}
			else if ($this->iMediumId == -1 && $this->iSection == 1)
			{
				$this->GetStatisticForManagerForYear($year);
				$this->GetStatisticForManagerForMonth($year, $month);
			}
		}
		else if ($mode == "by_month")
		{
			$this->GetStatisticForManagerForMonth($year, $month);
		}
	}
	
	/**
	 * GetStatisticForCustomerForYear
	 *	 
     * collecting statistical info about customer - 
	 * sum of debt, sum of payments by month, number of orders by month
	 *
	 * @param	int	$year	filter by year
     */
	function GetStatisticForCustomerForYear($year)
	{
		set_time_limit(0);
		
		// debt
		$sql = "SELECT SUM(debt) AS debt FROM press_track_new.order "
			 . "WHERE press_track_new.order.customer = $this->iUserId "
			 . "AND press_track_new.order.creation LIKE '%$year'";
		$query = mysql_query($sql);
		$res = mysql_fetch_object($query);		
		$this->obStatistic->dDebt = round($res->debt, 2);
		
		// sum of payments and num of orders
		for ($i=1; $i < 13; $i++) 
		{
			$month = $i;
			if ($i < 10)
			{
				$month = "0" . $month;
			}
			$date = $month . "/" . $year;
			
			$sql = "SELECT SUM(sum) AS sum_of_payment  FROM payment "
				 . "WHERE creation LIKE '%$date' AND payment.order "
				 . "IN (SELECT idorder FROM press_track_new.order "
				 . "WHERE press_track_new.order.customer = $this->iUserId)";
			$query_pay = mysql_query($sql);			
			$res = mysql_fetch_object($query_pay);			
			if ($res->sum_of_payment == 0)
			{
				$this->obStatistic->arSumOfPayment[] = 0;
			}
			else
			{
				$this->obStatistic->arSumOfPayment[] = round($res->sum_of_payment, 2);
			}
					
			$sql = "SELECT COUNT(idorder) AS num_of_order FROM press_track_new.order " 
				 . "WHERE press_track_new.order.customer = $this->iUserId "
				 . "AND press_track_new.order.creation LIKE '%$date'";
			$query_count = mysql_query($sql);			
			$res = mysql_fetch_object($query_count);			
			if ($res->num_of_order == 0)
			{
				$this->obStatistic->arNumOfOrder[] = 0;
			}
			else
			{
				$this->obStatistic->arNumOfOrder[] = $res->num_of_order;
			}					
		}
	}
	
	/**
	 * GetStatisticForManagerForYear
	 *	 
     * collecting statistical info about manager for year - 
	 * sum of debt, sum of payments by month, number of orders by month
	 *
	 * @param	int	$iduser	user ID
     */
	function GetStatisticForManagerForYear($year) 
	{
		set_time_limit(0);
		
		// debt
		$sql = "SELECT SUM(debt) AS debt FROM press_track_new.order "
			 . "WHERE creation LIKE '%$year' AND manager = $this->iUserId";
		$query = mysql_query($sql);		
		$res = mysql_fetch_object($query);
		$this->obStatistic->dDebt = round($res->debt, 2);
		
		// sum of payments and sum of orders
		for ($i=1; $i < 13; $i++) 
		{
			$month = $i;
			if ($i < 10)
			{
				$month = "0" . $month;
			}
			$date = $month . "/" . $year;
			
			$sql = "SELECT SUM(sum) AS sum_of_payment FROM payment "
				 . "WHERE creation LIKE '%$date' AND payment.order "
				 . "IN (SELECT idorder FROM press_track_new.order "
				 . "WHERE press_track_new.order.manager = $this->iUserId)";
			$query = mysql_query($sql);
			$res = mysql_fetch_object($query);
			if ($res->sum_of_payment == null)
			{
				$this->obStatistic->arSumOfPayment[] = 0;
			}
			else
			{
				$this->obStatistic->arSumOfPayment[] = round($res->sum_of_payment, 2);
			}
					
			$sql = "SELECT SUM(cost) AS sum_of_order FROM task "
				 . "WHERE task.order "
				 . "IN (SELECT idorder FROM press_track_new.order "
				 . "WHERE press_track_new.order.manager = $this->iUserId "
				 . "AND press_track_new.order.creation LIKE '%$date')";
			$query = mysql_query($sql);
			$res = mysql_fetch_object($query);
			if ($res->sum_of_order == null)
			{
				$this->obStatistic->arSumOfOrder[] = 0;
			}
			else
			{
				$this->obStatistic->arSumOfOrder[] = round($res->sum_of_order, 2);
			}				
		}			
	}
	
	/**
	 * GetStatisticForManagerForMonth
	 *	 
     * collecting statistical info about manager by month - 
	 * sum of debt, sum of payments by month, number of orders by month
	 *
	 * @param	int	$iduser	user ID
     */
	function GetStatisticForManagerForMonth($year, $month) 
	{
		$date = $month . "/" . $year;
		
		// num of orders, sum of orders, sum of payments
		$sql = "SELECT COUNT(idorder) AS num_of_order FROM press_track_new.order "
			 . "WHERE manager = $this->iUserId AND creation LIKE '%$date'";
		$query = mysql_query($sql);		
		if (mysql_errno())
		{
			return mysql_errno() . ": " . mysql_error();
		}
		$res = mysql_fetch_object($query);
		$this->obStatistic->iNumOfOrderForMonth = $res->num_of_order;
		
		$sql = "SELECT SUM(cost) AS sum_of_order FROM press_track_new.task " 
			 . "WHERE press_track_new.task.order IN "
			 . "(SELECT press_track_new.order.idorder FROM press_track_new.order "
			 . "WHERE manager = $this->iUserId AND creation LIKE '%$date')";
		$query = mysql_query($sql);		
		if (mysql_errno())
		{
			return mysql_errno() . ": " . mysql_error();
		}
		$res = mysql_fetch_object($query);
		$this->obStatistic->dSumOfCostForMonth = round($res->sum_of_order, 2);
		
		$sql = "SELECT SUM(sum) AS sum_of_payment FROM press_track_new.payment "
			 . "WHERE press_track_new.payment.creation LIKE '%$date' AND payment.order IN "
			 . "(SELECT idorder FROM press_track_new.order WHERE manager = $this->iUserId "
			 . "AND creation LIKE '%$year')";
		$query = mysql_query($sql);		
		if (mysql_errno())
		{
			return mysql_errno() . ": " . mysql_error();
		}
		$res = mysql_fetch_object($query);
		$this->obStatistic->dSumOfPaymentForMonth = round($res->sum_of_payment, 2);
		
		// customer by sum of orders & num of orders
		// customer by sum of payments & sum of debt		
		$sql = "SELECT DISTINCT iduser, name FROM press_track_new.user "
			 . "WHERE medium = $this->iUserId ";
		$query = mysql_query($sql);		
		if (mysql_errno())
		{
			return mysql_errno() . ": " . mysql_error();
		}
		
		$ar_of_customer = array();
		while ($res = mysql_fetch_object($query)) 
		{
			$ar_of_customer[] = $res;
		}
		
		foreach ($ar_of_customer as $customer) 
		{
			$sql = "SELECT SUM(cost) AS sum_of_order "
				 . "FROM press_track_new.task WHERE press_track_new.task.order IN "
				 . "(SELECT press_track_new.order.idorder FROM press_track_new.order "
				 . "WHERE creation LIKE '%$date' AND customer = $customer->iduser)";
			$query	= mysql_query($sql);
			$res	= mysql_fetch_object($query);
			$customer->total = round($res->sum_of_order, 2);
			if ($customer->total != 0)
			{
				$this->obStatistic->arCustBySumOfOrder[] = clone $customer;
			}
			
			$sql = "SELECT COUNT(idorder) AS num_of_order FROM press_track_new.order "
				 . "WHERE creation LIKE '%$date' AND customer = $customer->iduser";
			$query	= mysql_query($sql);
			$res	= mysql_fetch_object($query);	
			$customer->total = $res->num_of_order;
			if ($customer->total != 0)
			{
				$this->obStatistic->arCustByNumOfOrder[] = clone $customer;
			}
			
			$cust_by_payment_and_debt->name		= $customer->name;
			$cust_by_payment_and_debt->total1 	= 0;
			$cust_by_payment_and_debt->total2 	= 0;
			
			$sql = "SELECT SUM(sum) AS sum_of_payment FROM press_track_new.payment "
				 . "WHERE press_track_new.payment.creation LIKE '%$date' "
				 . "AND payment.order IN (SELECT idorder FROM press_track_new.order "
				 . "WHERE creation LIKE '%$year' AND customer = $customer->iduser)";
			$query	= mysql_query($sql);
			$res	= mysql_fetch_object($query);	
			$cust_by_payment_and_debt->total1 = round($res->sum_of_payment, 2);
			
			$sql = "SELECT SUM(debt) AS debt FROM press_track_new.order "
				 . "WHERE creation LIKE '%$year' AND customer = $customer->iduser";
			$query	= mysql_query($sql);
			$res	= mysql_fetch_object($query);
			if ($res->debt > 0)
				$cust_by_payment_and_debt->total2 = round($res->debt, 2);
			if ($cust_by_payment_and_debt->total1 != 0 
			 || $cust_by_payment_and_debt->total2 != 0)
			{
				$this->obStatistic->arCustByPaymentAndDebt[] = clone $cust_by_payment_and_debt;
			}
		}		
	}
	
	/**
	 * AddWorker
	 *	 
     * insert new worker with access to a section 
	 *
	 * :WARNING: password is stored in the database as md5 hash.
	 *
	 * :WARNING: value of medium for worker is -1
	 *
	 * :WARNING: list of all section
	 *  
	 * 		- 1 	- sales, 
	 * 		- 2 	- store, 
	 * 		- 3 	- prepress, 
	 * 		- 4 	- press, 
	 * 		- 5 	- postpress, 
	 * 		- 6 	- destination
	 * 		- 7 	- accounting, 
	 * 		- 8 	- customers 
	 * 		- 9 	- report 
	 *
	 * @param	string	$nick		nickname
	 * @param	string	$pass		password
	 * @param	string	$fullname	full name
	 * @param	int		$section	section	 
     * @return	string	$result		"successful" or error message
     */
	static function AddWorker($nick, $pass, $fullname, $section) 
	{
		$salt = UserClass::GetSalt();
		$sql = sprintf("INSERT INTO press_track_new.user "
			 . "(nick, pass, name, salt, section) "
			 . "VALUES ('%s', '%s', '%s', '%s', %d)", 
			 $nick, md5(md5($pass).$salt), $fullname, $salt, $section);			 
		$query = mysql_query($sql);
		if (mysql_errno()) 
		{
			return mysql_errno() . ": " . mysql_error();
		}
		
		$iduser = mysql_insert_id();
		$result = UserClass::SetAccess($iduser, $section);
		
		return $result;
	}
	
	/**
	 * AddCustomer
	 *	 
     * insert new customer
	 *
	 * :WARNING: by default customers do not have access to system.
	 *
	 * @param	int			$medium	the ID of the manager who is the mediator for this customer
	 * @return	int/string	$result	user ID or error message
     */
	static function AddCustomer($medium) 
	{
		$sql = sprintf("INSERT INTO press_track_new.user (name, section, medium) "
			 . "VALUES ('%s', %d, %d)", "new customer", 7, $medium);
		$query = mysql_query($sql);
		if (mysql_errno()) 
		{
			return mysql_errno() . ": " . mysql_error();
		}
		else
		{
			$result = mysql_insert_id();
			return $result;
		}
	}
	
	/**
	 * GetCustomers
	 *	 
     * return customers
	 *
	 * :WARNING: 
	 *
	 * @param	int			$manager	filter by mediator
	 * @param	int			$year		filter statistic by year
	 * @return	array		$result		array of users
     */
	static function GetCustomers($manager, $year) 
	{
		$sql = sprintf("SELECT iduser, name FROM press_track_new.user "
			 . "WHERE medium = $manager ORDER BY name");
		$query = mysql_query($sql);		
		if (mysql_errno())
		{
			return mysql_errno() . ": " . mysql_error();
		}
		
		set_time_limit(0);
		while ($cust = mysql_fetch_object($query)) 
		{
			$user = new UserClass();
			$user->ReadFromDb($cust->iduser);
			$user->GetContacts();
			$user->GetStatistic("by_year", date("Y"), date("m"));
			$result[] = $user;
		}
		return $result;
	}
	
	/**
	 * Update
	 *	 
     * update user's parameter
	 *
	 * @param	int		$iduser	user ID
	 * @param	string	$param	parameter
	 * @param	string	$value	new value
	 * @return	string	$result	"successful" or error message
     */	
	static function Update($iduser, $param, $value)
	{
		switch ($param) {
			case "name":
				$sql = sprintf("UPDATE press_track_new.user SET press_track_new.user.name = '%s' " 
					 . "WHERE iduser = %d", $value, $iduser);
			break;
		}		
		$query = mysql_query($sql);
		if (mysql_errno()) 
		{
			return mysql_errno() . ": " . mysql_error();
		}
		else 
		{
			return "successful";
		}
	}
	
	/**
	 * Login
	 *	 
     * user authorisation
	 *
	 * @param	string			$nick		user nickname
	 * @param	string			$pass		user password
	 * @param	int				$section	section
	 * @return	LoginResultVO	$result		
     */
	static function Login($nick, $pass, $section) 
	{
		$loginResult = new LoginResultVO();
		
		$sql = UserClass::Escape("SELECT * FROM press_track_new.user "
			 . "WHERE nick = '$nick' LIMIT 1");
		$query = mysql_query($sql);						
		if (mysql_errno()) 
		{
			$loginResult->message = mysql_errno() . ": " . mysql_error();
			return $loginResult;
		}		
		$res = mysql_fetch_object($query);
		
		$sql = sprintf("SELECT COUNT(user) AS cnt FROM press_track_new.access " 
			 . "WHERE user = %d AND section = '%s'", 
			 $res->iduser, $section);
		$query	= mysql_query($sql);		
		$cnt	= mysql_fetch_object($query);		
		if ($cnt->cnt != 1)
		{
			$loginResult->message = "Access denied";
			return $loginResult;
		}
		
		if ($res->pass == md5(md5($pass).$res->salt)) 
		{
			$loginResult->status 			= true;
			$loginResult->user 				= new UserClass();
			$loginResult->user->ReadFromDb($res->iduser);
			$loginResult->user->GetStatistic("by_year", date("Y"), date("m"));
		} 
		else 
		{
			$loginResult->message = "Login Failed please make sure that the username and password is correct.";		
		}		
		return $loginResult;
	}
	
	/**
	 * AddContact
	 *
	 * insert new user contact info
	 *
	 * @param	int			$iduser	user ID
	 * @param	int			$type	type of contact
	 * @param	string		$info	contact info
	 * return	int/string	$result	contact ID or error message	
	 */
	static function AddContact($iduser, $type, $info)
	{
		$sql = sprintf("INSERT INTO press_track_new.contacts (body, type, user) "
			 . "VALUES ('%s', %d, %d)", $info, $type, $iduser);
		$query = mysql_query($sql);
		$result = mysql_insert_id(); 
		if (mysql_errno())
		{
			return mysql_errno() . ": " . mysql_error();
		}
		else
		{
			return $result;
		}
	}
	
	/**
	 * UpdateContact
	 *
	 * update user contact info
	 *
	 * @param	int		$idcontact	contact ID
	 * @param	string	$info		contact info
	 * return	string	$result		"successful" or error message	
	 */
	static function UpdateContact($idcontact, $info) 
	{
		$sql = sprintf("UPDATE press_track_new.contacts "
			 . "SET press_track_new.contacts.body = '%s' "
			 . "WHERE press_track_new.contacts.idcontact = %d", 
			 $info, $idcontact);
		$query = mysql_query($sql);
		if (mysql_errno())
		{
			return mysql_errno() . ": " . mysql_error();
		}
		else
		{
			return "successful";
		}
	}
	
	/**
	 * RemoveContact
	 *
	 * remove user contact
	 *
	 * @param	int		$idcontact	contact ID
	 * return	string	$result		"successful" or error message	
	 */
	static function RemoveContact($idcontact) 
	{
		$sql = sprintf("DELETE FROM press_track_new.contacts "
			 . "WHERE idcontact = %d", $idcontact);
		$query = mysql_query($sql);
		if (mysql_errno())
		{
			return mysql_errno() . ": " . mysql_error();
		}
		else
		{
			return "successful"; 
		}
	}
	 
	/**
	 * GetSalt
	 *	 
     * generate random salt for md5 hash
	 *
	 * @access  private
	 *
	 * @return	string	$salt 
     */
	private static function GetSalt() 
	{
		$salt = '';
		$length = rand(5,10);
		for ($i=0; $i<$length; $i++) 
		{
			$salt .= chr(rand(33,126));
		}
		return $salt;
	}
	
	/**
	 * Escape
	 *	 
     * check sql
	 *
	 * @access  private
	 *
	 * @param	string	$sql	sql
	 * @return	string	$sql 
     */
	private static function Escape($sql) {
		$args = func_get_args();
		foreach($args as $key => $val)
		{
			$args[$key] = mysql_real_escape_string($val);
		}
		$args[0] = $sql;
		return call_user_func_array('sprintf', $args);
	}	
	
	/**
	 * SetAccess
	 *	 
     * set access to a section for user 
	 *
	 * @access  private
	 *
	 * @param	int		$iduser		user ID
	 * @param	int		$section	section	 
     * @return	string	$result		"successful" or error message
     */
	public static function SetAccess($iduser, $section) 
	{
		$sql = sprintf("INSERT INTO press_track_new.access "
			 . "(user, section) VALUES (%d, %d)", 
			 $iduser, $section);
		$query = mysql_query($sql);
		if (mysql_errno())
		{
			return mysql_errno() . ": " . mysql_error();
		}
		else
		{
			return "successful";
		}
	}
}
?>